﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Reflection;
using System.IO;
using System.Diagnostics;
using Microsoft.Office.Interop.Excel;

namespace template_dll
{
    public partial class ChildForm : Form
    {
        public ChildForm()
        {
            InitializeComponent();
        }

        private const string namechcol = "Флаг";
        private SqlConnection conn;
        private System.Data.DataTable[] dt_rep = new System.Data.DataTable[20];
        private delegate void Deleg();
        private Deleg del;
        private Deleg del_exc;

        private struct date_str { public DateTime dt; public string dtname; }
        private date_str[] dt_str = new date_str[] { };
        private string month;
        private DateTime ldt7;
        private DateTime ldt6;
        private DateTime ldt5;
        private DateTime ldt4;
        private DateTime ldt3;
        private DateTime ldt2;
        private DateTime ldt1;

        public static bool DisplayForm(System.Windows.Forms.Form theMDIParent)
        {
            ChildForm f = new ChildForm();
            f.MdiParent = theMDIParent;
            f.WindowState = FormWindowState.Maximized;
            f.Show();
            return true;
        }

        private string GetServ(string filename)
        {
            string str = "";
            using (StreamReader streamReader = new StreamReader(filename))
            {
                while (!streamReader.EndOfStream)
                {
                    str += streamReader.ReadLine();
                }
            }
            return str;
        }

        private void ChildForm_Load(object sender, EventArgs e)
        {
            conn = new SqlConnection(@"Server=" + GetServ("serv.txt") + ";Database=PharmOLAPDW;Integrated Security=True");

            SqlCommand getdate = conn.CreateCommand();
            getdate.CommandText = @"pas_MonthSel";
            getdate.CommandType = CommandType.StoredProcedure;
            System.Data.DataTable date = new System.Data.DataTable();
            if (conn.State == ConnectionState.Closed) conn.Open();
            date.Load(getdate.ExecuteReader());
            if (date.Rows.Count > 0)
            {
                foreach (DataRow dr in date.Rows)
                {
                    Array.Resize(ref dt_str, dt_str.Length + 1);
                    dt_str[dt_str.Length - 1].dt = Convert.ToDateTime(dr[0]);
                    dt_str[dt_str.Length - 1].dtname = dr[1].ToString();
                    comboBox1.Items.Add(dr[1].ToString());
                    if (DateTime.Now.Date.Month == Convert.ToDateTime(dr[0]).Month)
                        comboBox1.SelectedIndex = comboBox1.Items.Count - 1;
                }

            }
        }

        private void StartCreateRep(object sender, EventArgs e)
        {
            month = comboBox1.Text;
            del = new Deleg(CreateRep);
            this.Text += " Формируется";
            IAsyncResult asyncRes = del.BeginInvoke(new AsyncCallback(CallBackFunc), null); //
            panel1.Enabled = false;
            dataGridView1.Enabled = false;
        }

        private void CallBackFunc(IAsyncResult aRes)
        {
            del.EndInvoke(aRes);
            panel1.Invoke((Func<bool>)(() => panel1.Enabled = true));
            dataGridView1.Invoke((Func<bool>)(() => dataGridView1.Enabled = true));
            this.Invoke((Func<string>)(() => this.Text = this.Text.Replace(" Формируется", "")));
            //dataGridView1.Invoke((Func<object>)(() => dataGridView1.DataSource = dt_rep.DefaultView));
        }

        private void CreateRep()
        {
            if (conn.State == ConnectionState.Closed) conn.Open();
            try
            {
                for (int i = 10; i < 11; i++) // ***********0(10),11********************************************************************************************
                {
                    dt_rep[i] = new System.Data.DataTable();
                    //Console.WriteLine("Step1");
                    dt_rep[i].Load(RepProc(conn,i+1));
                    //Console.WriteLine("Step2");
                }
                conn.Close();
                CreateExcel();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                conn.Close();
            }

        }

        private SqlDataReader RepProc(SqlConnection sqlcon,int b)//метод для вызова хранимки
        {
            SqlCommand sqlcom = sqlcon.CreateCommand();
            sqlcom.CommandType = CommandType.StoredProcedure;
            sqlcom.CommandText = @"Vad_OperPok"; //имя хранимой процедуры
            sqlcom.CommandTimeout = 600;
           
            sqlcom.Parameters.AddWithValue("@num_page", b);

            for (int i = 0; i < dt_str.Length; i++)
            {
                if (dt_str[i].dtname == month)
                {
                    sqlcom.Parameters.AddWithValue("@month_year",dt_str[i].dt);
                    if (dt_str[i].dt.Month != DateTime.Today.Month)
                    {
                        ldt7 = dt_str[i].dt.AddMonths(1).AddDays(-1);
                        ldt6 = dt_str[i].dt.AddMonths(1).AddDays(-2);
                        ldt5 = dt_str[i].dt.AddMonths(1).AddDays(-3);
                        ldt4 = dt_str[i].dt.AddMonths(1).AddDays(-4);
                        ldt3 = dt_str[i].dt.AddMonths(1).AddDays(-5);
                        ldt2 = dt_str[i].dt.AddMonths(1).AddDays(-6);
                        ldt1 = dt_str[i].dt.AddMonths(1).AddDays(-7);
                    }
                    else
                    {
                        ldt7 = DateTime.Today.AddDays(-2);
                        ldt6 = DateTime.Today.AddDays(-3);
                        ldt5 = DateTime.Today.AddDays(-4);
                        ldt4 = DateTime.Today.AddDays(-5);
                        ldt3 = DateTime.Today.AddDays(-6);
                        ldt2 = DateTime.Today.AddDays(-7);
                        ldt1 = DateTime.Today.AddDays(-8);
                    }
                }
            }

            return sqlcom.ExecuteReader();
        }

        private void ExpToExcel(object sender, EventArgs e)//экспорт в Excel
        {
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.FileName = "csv-file";
            sfd.Filter = "Файл CSV | *.csv";
            if (sfd.ShowDialog() == DialogResult.OK)
            {   
                SaveDGVToCSVfile(sfd.FileName.ToString(), dataGridView1, true);
            }
        }

        public static bool SaveDGVToCSVfile(string filename, DataGridView table, bool openInExcel)
        {
            try
            {
                StreamWriter sw = new StreamWriter(filename, false, Encoding.Unicode);

                List<int> col_n = new List<int>();
                foreach (DataGridViewColumn col in table.Columns)
                    if (col.Visible)
                    {
                        sw.Write(col.HeaderText + "\t");
                        col_n.Add(col.Index);
                    }
                sw.WriteLine();
                int x = table.RowCount;
                if (table.AllowUserToAddRows) x--;

                for (int i = 0; i < x; i++)
                {
                    for (int y = 0; y < col_n.Count; y++)
                        sw.Write(table[col_n[y], i].Value + "\t");
                    sw.Write(" \r\n");
                }
                sw.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                return false;
            }

            if (openInExcel)
            {
                try
                {
                    Process newProc = Process.Start("excel.exe", "\"" + filename + "\"");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                }
            }
            return true;
        }

        private void button1_Click(object sender, EventArgs e)
        {

        }

        private void button2_Click(object sender, EventArgs e)
        {
            del_exc = new Deleg(CreateExcel);
            IAsyncResult asyncRes = del_exc.BeginInvoke(new AsyncCallback(CallBackFuncExc), null); //

        }

        private void CreateExcel()//создание и заполнение книги Excel
        {
            Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application();    // создаем COM объект Excel
            try
            {
                //oExcel.ScreenUpdating = false; // выключаем обновление страниц
                oExcel.Visible = true;    // включаем видимость объекта
                Microsoft.Office.Interop.Excel.Workbook oExcelBook;
                Microsoft.Office.Interop.Excel.Worksheet oExcelSheet;
                Microsoft.Office.Interop.Excel.Range oExlRange;
                
                oExcel.Workbooks.Add(Type.Missing);    // создаем  книгу
                oExcel.ActiveWindow.WindowState = XlWindowState.xlNormal;    // задаем состояние окна
                oExcel.Sheets.Add(Type.Missing, Type.Missing, 11, Type.Missing);    // добавляем 12 листов
                oExcelBook = oExcel.Workbooks[1] as Microsoft.Office.Interop.Excel.Workbook;    // создаем объект oExcelBook
                oExcelSheet = oExcel.Sheets[1] as Microsoft.Office.Interop.Excel.Worksheet;     // создаем объект oExcelSheet
                oExcelSheet.Name = "Свод по регионам присутствия";
                oExcelSheet = oExcel.Sheets[2] as Microsoft.Office.Interop.Excel.Worksheet;
                oExcelSheet.Name = "Свод по регионам ИТОГИ";
                oExcelSheet = oExcel.Sheets[3] as Microsoft.Office.Interop.Excel.Worksheet;
                oExcelSheet.Name = "Свод по управлениям ИТОГИ";
                oExcelSheet = oExcel.Sheets[4] as Microsoft.Office.Interop.Excel.Worksheet;
                oExcelSheet.Name = "Региональное управление Центр№1";
                oExcelSheet = oExcel.Sheets[5] as Microsoft.Office.Interop.Excel.Worksheet;
                oExcelSheet.Name = "Региональное управление Центр№2";
                oExcelSheet = oExcel.Sheets[6] as Microsoft.Office.Interop.Excel.Worksheet;
                oExcelSheet.Name = "Региональное управление Центр№3";
                oExcelSheet = oExcel.Sheets[7] as Microsoft.Office.Interop.Excel.Worksheet;
                oExcelSheet.Name = "Региональное управление Центр№4";
                oExcelSheet = oExcel.Sheets[8] as Microsoft.Office.Interop.Excel.Worksheet;
                oExcelSheet.Name = "Региональное управление Центр№5";
                oExcelSheet = oExcel.Sheets[9] as Microsoft.Office.Interop.Excel.Worksheet;
                oExcelSheet.Name = "Региональное управление Сибирь";
                oExcelSheet = oExcel.Sheets[10] as Microsoft.Office.Interop.Excel.Worksheet;
                oExcelSheet.Name = "Региональное управление ЮГ";
                oExcelSheet = oExcel.Sheets[11] as Microsoft.Office.Interop.Excel.Worksheet;
                oExcelSheet.Name = "Свод по сети";

                // ОФОРМЛЯЕМ ЛИСТЫ(11)
                for (int il = 11; il <= 11; il++) // ***************1(11),11****************************************************************************************
                {
                    // выбираем нужную страницу
                    oExcelSheet = oExcel.Sheets[il] as Microsoft.Office.Interop.Excel.Worksheet;
                    ((Microsoft.Office.Interop.Excel._Worksheet)oExcelSheet).Activate();
                    oExcel.ScreenUpdating = true;   // включаем обновляемость

                    // подписываем колонки
                    oExcelSheet.Cells[1, "A"] = "";
                    oExcelSheet.Cells[2, "A"] = "";
                    oExcelSheet.Cells[3, "A"] = "№ МА";
                    oExcelSheet.Cells[3, "B"] = "Наименование аптеки";
                    oExcelSheet.Cells[3, "C"] = "Юр.лицо";
                    oExcelSheet.Cells[3, "D"] = "Регион";
                    oExcelSheet.Cells[3, "E"] = "Дата открытия";
                    oExcelSheet.Cells[3, "F"] = "Руководитель";
                    oExcelSheet.Cells[3, "G"] = "Ценообразование";
                    oExcelSheet.Cells[3, "H"] = "Направление бизнеса";
                    oExcelSheet.Cells[3, "I"] = "Вид трафика";
                    oExcelSheet.Cells[3, "J"] = "Категория аптеки";

                    oExcelSheet.Cells[1, "K"] = "Выручка тыс. рублей (" + month + ")";
                    oExcelSheet.Cells[3, "K"] = "Бюджет на текущий месяц";
                    oExcelSheet.Cells[3, "L"] = "План ОПП";
                    oExcelSheet.Cells[3, "M"] = "1 неделя";
                    oExcelSheet.Cells[3, "N"] = "2 неделя";
                    oExcelSheet.Cells[3, "O"] = "3 неделя";
                    oExcelSheet.Cells[3, "P"] = "4 неделя";
                    oExcelSheet.Cells[3, "Q"] = "Факт текущего месяца";
                    oExcelSheet.Cells[3, "R"] = ldt1.ToString("yyyy-MM-dd");
                    oExcelSheet.Cells[3, "S"] = ldt2.ToString("yyyy-MM-dd");
                    oExcelSheet.Cells[3, "T"] = ldt3.ToString("yyyy-MM-dd");
                    oExcelSheet.Cells[3, "U"] = ldt4.ToString("yyyy-MM-dd");
                    oExcelSheet.Cells[3, "V"] = ldt5.ToString("yyyy-MM-dd");
                    oExcelSheet.Cells[3, "W"] = ldt6.ToString("yyyy-MM-dd");
                    oExcelSheet.Cells[3, "X"] = ldt7.ToString("yyyy-MM-dd");
                    oExcelSheet.Cells[3, "Y"] = "Прошлый месяц факт продаж (полный месяц)";

                    oExcelSheet.Cells[3, "Z"] = "Прогноз текущего месяца";
                    oExcelSheet.Cells[3, "AA"] = "% вып. прогноз ОПП";
                    oExcelSheet.Cells[3, "AB"] = "% вып. прогноз Бюджета";
                    oExcelSheet.Cells[3, "AC"] = "Прирост к прошлому месяцу,%";
                    oExcelSheet.Cells[3, "AD"] = "Прирост к аналогичному периоду прошлого год,%";

                    oExcelSheet.Cells[1, "AE"] = "Сред.дн. продажи тыс. рублей";
                    oExcelSheet.Cells[3, "AE"] = "Прошлый месяц (факт)";
                    oExcelSheet.Cells[3, "AF"] = "Факт те  кущего месяца";
                    oExcelSheet.Cells[3, "AG"] = "Прирост к прошлому месяцу,%";

                    oExcelSheet.Cells[2, "AH"] = "Количество клиентов";
                    oExcelSheet.Cells[3, "AH"] = "Факт";
                    oExcelSheet.Cells[3, "AI"] = "Прирост к прошлому месяцу,%";
                    oExcelSheet.Cells[3, "AJ"] = "Прирост к аналогичному периоду прошлого года,%";

                    oExcelSheet.Cells[2, "AK"] = "Конвертация";
                    oExcelSheet.Cells[3, "AK"] = "План, %";
                    oExcelSheet.Cells[3, "AL"] = "Факт, %";
                    oExcelSheet.Cells[3, "AM"] = "Прирост к прошлому месяцу,%";
                    oExcelSheet.Cells[3, "AN"] = "Прирост к аналогичному периоду прошлого года,%";

                    oExcelSheet.Cells[2, "AO"] = "Средний чек";
                    oExcelSheet.Cells[3, "AO"] = "План";
                    oExcelSheet.Cells[3, "AP"] = "Факт";
                    oExcelSheet.Cells[3, "AQ"] = "Прирост к прошлому месяцу,%";
                    oExcelSheet.Cells[3, "AR"] = "Прирост к аналогичному периоду прошлого года,%";

                    oExcelSheet.Cells[2, "AS"] = "Кратность чека";
                    oExcelSheet.Cells[3, "AS"] = "Факт текушего месяца";
                    oExcelSheet.Cells[3, "AT"] = "Прирост к прошлому месяцу,%";
                    oExcelSheet.Cells[3, "AU"] = "Прирост к аналогичному периоду прошлого года,%";

                    oExcelSheet.Cells[2, "AV"] = "Наполняемость чека";
                    oExcelSheet.Cells[3, "AV"] = "Факт текушего месяца";
                    oExcelSheet.Cells[3, "AW"] = "Прирост к прошлому месяцу,%";
                    oExcelSheet.Cells[3, "AX"] = "Прирост к аналогичному периоду прошлого года,%";

                    oExcelSheet.Cells[2, "AY"] = "%РН";
                    oExcelSheet.Cells[3, "AY"] = "%РН факт текущего месяца";
                    oExcelSheet.Cells[3, "AZ"] = "%РН Бюджет текущего месяца";
                    oExcelSheet.Cells[3, "BA"] = "Выполнение бюджета текущего месяца";
                    oExcelSheet.Cells[3, "BB"] = "%РН факт прошлый месяц";
                    oExcelSheet.Cells[3, "BC"] = "Прирост к прошлому месяцу,%";

                    oExcelSheet.Cells[2, "BD"] = "Сумма РН тыс. рублей";
                    oExcelSheet.Cells[3, "BD"] = "Факт текущего месяца";
                    oExcelSheet.Cells[3, "BE"] = "Бюджет текущего месяца";
                    oExcelSheet.Cells[3, "BF"] = "Выполнение бюджета текущего месяца";
                    oExcelSheet.Cells[3, "BG"] = "Факт прошлый месяц";
                    oExcelSheet.Cells[3, "BH"] = "Прирост к прошлому месяцу,%";
                    oExcelSheet.Cells[3, "BI"] = "Прирост к аналогичному периоду прошлого года,%";

                    oExcelSheet.Cells[1, "BJ"] = "Дополнительно";
                    oExcelSheet.Cells[3, "BJ"] = "Дефектура на начало недели";
                    oExcelSheet.Cells[3, "BK"] = "Кол-во позиций ассортимента в аптеке на начало недели";
                    oExcelSheet.Cells[3, "BL"] = "Кол-во позиций ассортимента в аптеке на 01 чсило прошлого месяца";
                    oExcelSheet.Cells[3, "BM"] = "Отклонения";
                    oExcelSheet.Cells[3, "BN"] = "Оборачиваемость недельная";

                    // шрифт
                    oExlRange = oExcelSheet.get_Range("A1","BN3");
                    oExlRange.Font.Name = "Times New Roman";
                    oExlRange.Font.Bold = true;
                    oExlRange.Font.Size = 9;
                    // закрашиваем шапку
                    oExlRange.Interior.Color = ColorTranslator.ToOle(Color.SkyBlue);
                    // рамки в шапке
                    oExlRange.Borders.LineStyle = 1;

                    // высота и перенос
                    oExlRange = oExcelSheet.get_Range("1:2", Type.Missing);
                    oExlRange.RowHeight = 12.75;
                    oExlRange = oExcelSheet.get_Range("3:3", Type.Missing);
                    oExlRange.RowHeight = 75;
                    oExlRange.WrapText = true;
                    oExlRange = oExcelSheet.get_Range("4:400", Type.Missing);
                    oExlRange.RowHeight = 12.75;

                    // объединяем ячейки
                    oExlRange = oExcelSheet.get_Range("A1", "A3");
                    oExlRange.MergeCells = true;
                    oExlRange = oExcelSheet.get_Range("B1", "B3");
                    oExlRange.MergeCells = true;
                    oExlRange = oExcelSheet.get_Range("C1", "C3");
                    oExlRange.MergeCells = true;
                    oExlRange = oExcelSheet.get_Range("D1", "D3");
                    oExlRange.MergeCells = true;
                    oExlRange = oExcelSheet.get_Range("E1", "E3");
                    oExlRange.MergeCells = true;
                    oExlRange = oExcelSheet.get_Range("F1", "F3");
                    oExlRange.MergeCells = true;
                    oExlRange = oExcelSheet.get_Range("G1", "G3");
                    oExlRange.MergeCells = true;
                    oExlRange = oExcelSheet.get_Range("H1", "H3");
                    oExlRange.MergeCells = true;
                    oExlRange = oExcelSheet.get_Range("I1", "I3");
                    oExlRange.MergeCells = true;
                    oExlRange = oExcelSheet.get_Range("J1", "J3");
                    oExlRange.MergeCells = true;
                    oExlRange = oExcelSheet.get_Range("K1", "AD2");
                    oExlRange.MergeCells = true;
                    oExlRange = oExcelSheet.get_Range("AE1", "AG2");
                    oExlRange.MergeCells = true;
                    oExlRange = oExcelSheet.get_Range("AH1", "AX1");
                    oExlRange.MergeCells = true;
                    oExlRange = oExcelSheet.get_Range("AH2", "AJ2");
                    oExlRange.MergeCells = true;
                    oExlRange = oExcelSheet.get_Range("AK2", "AN2");
                    oExlRange.MergeCells = true;
                    oExlRange = oExcelSheet.get_Range("AO2", "AR2");
                    oExlRange.MergeCells = true;
                    oExlRange = oExcelSheet.get_Range("AS2", "AU2");
                    oExlRange.MergeCells = true;
                    oExlRange = oExcelSheet.get_Range("AV2", "AX2");
                    oExlRange.MergeCells = true;
                    oExlRange = oExcelSheet.get_Range("AY1", "BI1");
                    oExlRange.MergeCells = true;
                    oExlRange = oExcelSheet.get_Range("AY2", "BC2");
                    oExlRange.MergeCells = true;
                    oExlRange = oExcelSheet.get_Range("BD2", "BI2");
                    oExlRange.MergeCells = true;
                    oExlRange = oExcelSheet.get_Range("BJ1", "BN2");
                    oExlRange.MergeCells = true;

                    // выравнивание
                    oExlRange = oExcelSheet.get_Range("A1", "BN3");
                    oExlRange.VerticalAlignment = 2;
                    oExlRange.HorizontalAlignment = 3;

                    // задаем ширины колонок
                    oExlRange = oExcelSheet.get_Range("A:A", Type.Missing);
                    oExlRange.ColumnWidth = 4;
                    oExlRange = oExcelSheet.get_Range("B:B", Type.Missing);
                    oExlRange.ColumnWidth = 36;
                    oExlRange = oExcelSheet.get_Range("C:C", Type.Missing);
                    oExlRange.ColumnWidth = 22;
                    oExlRange = oExcelSheet.get_Range("D:D", Type.Missing);
                    oExlRange.ColumnWidth = 18;
                    oExlRange = oExcelSheet.get_Range("E:E", Type.Missing);
                    oExlRange.ColumnWidth = 9.3;
                    oExlRange = oExcelSheet.get_Range("F:G", Type.Missing);
                    oExlRange.ColumnWidth = 13.5;
                    oExlRange = oExcelSheet.get_Range("H:H", Type.Missing);
                    oExlRange.ColumnWidth = 11;
                    oExlRange = oExcelSheet.get_Range("BK:BL", Type.Missing);
                    oExlRange.ColumnWidth = 12.5;

                    // хайдим ненужные колонки
                    if (il == 2 || il == 3)
                    {
                        //MessageBox.Show("1");
                        oExlRange = oExcelSheet.get_Range("A:A", Type.Missing);
                        oExlRange.Columns.Hidden = true;
                        oExlRange = oExcelSheet.get_Range("C:J", Type.Missing);
                        oExlRange.Columns.Hidden = true;
                    }
                    if (il == 2)
                    {
                        oExcelSheet.Cells[1, "B"] = "Регион";
                    }
                    if (il == 3)
                    {
                        oExcelSheet.Cells[1, "B"] = "Управление";
                    }

                    // ЗАПОЛНЯЕМ ЛИСТ
                    int lnCountRow = dt_rep[il - 1].Rows.Count;
                    int ic3;
                    for (int ic = 1; ic <= lnCountRow; ic++) // идем по строкам
                    {
                        if (ic > 40)
                        {
                            oExcel.ScreenUpdating = false;   // ВЫключаем обновляемость
                        }
                        ic3 = ic + 3;
                        for (int ifi = 1; ifi <= 66; ifi++) // идем по колонкам
                        {
                            // зажирняем итоги
                            if (ifi == 1 && Convert.ToInt16(dt_rep[il - 1].Rows[ic - 1].ItemArray[0]) == 0 && il != 2 && il != 3)
                            {
                                // MessageBox.Show(lcAddrCell);
                                string lcAddrCell = ic3.ToString() + ":" + ic3.ToString();
                                oExlRange = oExcelSheet.get_Range(lcAddrCell, Type.Missing);
                                oExlRange.Font.Bold = true;
                            }
                            else
                            {
                                // ЗАПОЛНЯЕМ ЯЧЕЙКУ
                                oExcelSheet.Cells[ic3, ifi] = dt_rep[il - 1].Rows[ic - 1].ItemArray[ifi - 1];
                            }
                            // дорисовываем
                            if (ifi == 1)
                            {
                                //закрасняем провалы
                                if (!Convert.IsDBNull(dt_rep[il - 1].Rows[ic - 1].ItemArray[26]) && Convert.ToDouble(dt_rep[il - 1].Rows[ic - 1].ItemArray[26]) < 97)
                                {
                                    string lcAddrCell = "AA" + ic3.ToString();
                                    oExlRange = oExcelSheet.get_Range(lcAddrCell, Type.Missing);
                                    oExlRange.Font.Color = ColorTranslator.ToOle(Color.Red);
                                }
                                if (!Convert.IsDBNull(dt_rep[il - 1].Rows[ic - 1].ItemArray[27]) && Convert.ToDouble(dt_rep[il - 1].Rows[ic - 1].ItemArray[27]) < 97)
                                {
                                    string lcAddrCell = "AB" + ic3.ToString();
                                    oExlRange = oExcelSheet.get_Range(lcAddrCell, Type.Missing);
                                    oExlRange.Font.Color = ColorTranslator.ToOle(Color.Red);
                                }
                                if (!Convert.IsDBNull(dt_rep[il - 1].Rows[ic - 1].ItemArray[28]) && Convert.ToDouble(dt_rep[il - 1].Rows[ic - 1].ItemArray[28]) < 0)
                                {
                                    string lcAddrCell = "AC" + ic3.ToString();
                                    oExlRange = oExcelSheet.get_Range(lcAddrCell, Type.Missing);
                                    oExlRange.Font.Color = ColorTranslator.ToOle(Color.Red);
                                }
                                if (!Convert.IsDBNull(dt_rep[il - 1].Rows[ic - 1].ItemArray[29]) && Convert.ToDouble(dt_rep[il - 1].Rows[ic - 1].ItemArray[29]) < 0)
                                {
                                    string lcAddrCell = "AD" + ic3.ToString();
                                    oExlRange = oExcelSheet.get_Range(lcAddrCell, Type.Missing);
                                    oExlRange.Font.Color = ColorTranslator.ToOle(Color.Red);
                                }
                                if (!Convert.IsDBNull(dt_rep[il - 1].Rows[ic - 1].ItemArray[32]) && Convert.ToDouble(dt_rep[il - 1].Rows[ic - 1].ItemArray[32]) < 0)
                                {
                                    string lcAddrCell = "AG" + ic3.ToString();
                                    oExlRange = oExcelSheet.get_Range(lcAddrCell, Type.Missing);
                                    oExlRange.Font.Color = ColorTranslator.ToOle(Color.Red);
                                }
                                if (!Convert.IsDBNull(dt_rep[il - 1].Rows[ic - 1].ItemArray[34]) && Convert.ToDouble(dt_rep[il - 1].Rows[ic - 1].ItemArray[34]) < 0)
                                {
                                    string lcAddrCell = "AI" + ic3.ToString();
                                    oExlRange = oExcelSheet.get_Range(lcAddrCell, Type.Missing);
                                    oExlRange.Font.Color = ColorTranslator.ToOle(Color.Red);
                                }
                                if (!Convert.IsDBNull(dt_rep[il - 1].Rows[ic - 1].ItemArray[35]) && Convert.ToDouble(dt_rep[il - 1].Rows[ic - 1].ItemArray[35]) < 0)
                                {
                                    string lcAddrCell = "AJ" + ic3.ToString();
                                    oExlRange = oExcelSheet.get_Range(lcAddrCell, Type.Missing);
                                    oExlRange.Font.Color = ColorTranslator.ToOle(Color.Red);
                                }
                                if (!Convert.IsDBNull(dt_rep[il - 1].Rows[ic - 1].ItemArray[37]) && !Convert.IsDBNull(dt_rep[il - 1].Rows[ic - 1].ItemArray[36]))
                                {
                                    if (Convert.ToDouble(dt_rep[il - 1].Rows[ic - 1].ItemArray[37]) < Convert.ToDouble(dt_rep[il - 1].Rows[ic - 1].ItemArray[36]))
                                    {
                                        string lcAddrCell = "AL" + ic3.ToString();
                                        oExlRange = oExcelSheet.get_Range(lcAddrCell, Type.Missing);
                                        oExlRange.Font.Color = ColorTranslator.ToOle(Color.Red);
                                    }
                                }
                                if (!Convert.IsDBNull(dt_rep[il - 1].Rows[ic - 1].ItemArray[38]) && Convert.ToDouble(dt_rep[il - 1].Rows[ic - 1].ItemArray[38]) < 0)
                                {
                                    string lcAddrCell = "AM" + ic3.ToString();
                                    oExlRange = oExcelSheet.get_Range(lcAddrCell, Type.Missing);
                                    oExlRange.Font.Color = ColorTranslator.ToOle(Color.Red);
                                }
                                if (!Convert.IsDBNull(dt_rep[il - 1].Rows[ic - 1].ItemArray[39]) && Convert.ToDouble(dt_rep[il - 1].Rows[ic - 1].ItemArray[39]) < 0)
                                {
                                    string lcAddrCell = "AN" + ic3.ToString();
                                    oExlRange = oExcelSheet.get_Range(lcAddrCell, Type.Missing);
                                    oExlRange.Font.Color = ColorTranslator.ToOle(Color.Red);
                                }
                                if (!Convert.IsDBNull(dt_rep[il - 1].Rows[ic - 1].ItemArray[41]) && !Convert.IsDBNull(dt_rep[il - 1].Rows[ic - 1].ItemArray[40]))
                                {
                                    if (Convert.ToDouble(dt_rep[il - 1].Rows[ic - 1].ItemArray[41]) < Convert.ToDouble(dt_rep[il - 1].Rows[ic - 1].ItemArray[40]))
                                    {
                                        string lcAddrCell = "AP" + ic3.ToString();
                                        oExlRange = oExcelSheet.get_Range(lcAddrCell, Type.Missing);
                                        oExlRange.Font.Color = ColorTranslator.ToOle(Color.Red);
                                    }
                                }
                                if (!Convert.IsDBNull(dt_rep[il - 1].Rows[ic - 1].ItemArray[42]) && Convert.ToDouble(dt_rep[il - 1].Rows[ic - 1].ItemArray[42]) < 0)
                                {
                                    string lcAddrCell = "AQ" + ic3.ToString();
                                    oExlRange = oExcelSheet.get_Range(lcAddrCell, Type.Missing);
                                    oExlRange.Font.Color = ColorTranslator.ToOle(Color.Red);
                                }
                                if (!Convert.IsDBNull(dt_rep[il - 1].Rows[ic - 1].ItemArray[43]) && Convert.ToDouble(dt_rep[il - 1].Rows[ic - 1].ItemArray[43]) < 0)
                                {
                                    string lcAddrCell = "AR" + ic3.ToString();
                                    oExlRange = oExcelSheet.get_Range(lcAddrCell, Type.Missing);
                                    oExlRange.Font.Color = ColorTranslator.ToOle(Color.Red);
                                }
                                if (!Convert.IsDBNull(dt_rep[il - 1].Rows[ic - 1].ItemArray[45]) && Convert.ToDouble(dt_rep[il - 1].Rows[ic - 1].ItemArray[45]) < 0)
                                {
                                    string lcAddrCell = "AT" + ic3.ToString();
                                    oExlRange = oExcelSheet.get_Range(lcAddrCell, Type.Missing);
                                    oExlRange.Font.Color = ColorTranslator.ToOle(Color.Red);
                                }
                                if (!Convert.IsDBNull(dt_rep[il - 1].Rows[ic - 1].ItemArray[46]) && Convert.ToDouble(dt_rep[il - 1].Rows[ic - 1].ItemArray[46]) < 0)
                                {
                                    string lcAddrCell = "AU" + ic3.ToString();
                                    oExlRange = oExcelSheet.get_Range(lcAddrCell, Type.Missing);
                                    oExlRange.Font.Color = ColorTranslator.ToOle(Color.Red);
                                }
                                if (!Convert.IsDBNull(dt_rep[il - 1].Rows[ic - 1].ItemArray[48]) && Convert.ToDouble(dt_rep[il - 1].Rows[ic - 1].ItemArray[48]) < 0)
                                {
                                    string lcAddrCell = "AW" + ic3.ToString();
                                    oExlRange = oExcelSheet.get_Range(lcAddrCell, Type.Missing);
                                    oExlRange.Font.Color = ColorTranslator.ToOle(Color.Red);
                                }
                                if (!Convert.IsDBNull(dt_rep[il - 1].Rows[ic - 1].ItemArray[49]) && Convert.ToDouble(dt_rep[il - 1].Rows[ic - 1].ItemArray[49]) < 0)
                                {
                                    string lcAddrCell = "AX" + ic3.ToString();
                                    oExlRange = oExcelSheet.get_Range(lcAddrCell, Type.Missing);
                                    oExlRange.Font.Color = ColorTranslator.ToOle(Color.Red);
                                }
                                if (!Convert.IsDBNull(dt_rep[il - 1].Rows[ic - 1].ItemArray[52]) && Convert.ToDouble(dt_rep[il - 1].Rows[ic - 1].ItemArray[52]) < 0)
                                {
                                    string lcAddrCell = "BA" + ic3.ToString();
                                    oExlRange = oExcelSheet.get_Range(lcAddrCell, Type.Missing);
                                    oExlRange.Font.Color = ColorTranslator.ToOle(Color.Red);
                                }
                                if (!Convert.IsDBNull(dt_rep[il - 1].Rows[ic - 1].ItemArray[54]) && Convert.ToDouble(dt_rep[il - 1].Rows[ic - 1].ItemArray[54]) < 0)
                                {
                                    string lcAddrCell = "BC" + ic3.ToString();
                                    oExlRange = oExcelSheet.get_Range(lcAddrCell, Type.Missing);
                                    oExlRange.Font.Color = ColorTranslator.ToOle(Color.Red);
                                }
                                if (!Convert.IsDBNull(dt_rep[il - 1].Rows[ic - 1].ItemArray[57]) && Convert.ToDouble(dt_rep[il - 1].Rows[ic - 1].ItemArray[57]) < 0)
                                {
                                    string lcAddrCell = "BF" + ic3.ToString();
                                    oExlRange = oExcelSheet.get_Range(lcAddrCell, Type.Missing);
                                    oExlRange.Font.Color = ColorTranslator.ToOle(Color.Red);
                                }
                                if (!Convert.IsDBNull(dt_rep[il - 1].Rows[ic - 1].ItemArray[59]) && Convert.ToDouble(dt_rep[il - 1].Rows[ic - 1].ItemArray[59]) < 0)
                                {
                                    string lcAddrCell = "BH" + ic3.ToString();
                                    oExlRange = oExcelSheet.get_Range(lcAddrCell, Type.Missing);
                                    oExlRange.Font.Color = ColorTranslator.ToOle(Color.Red);
                                }
                                if (!Convert.IsDBNull(dt_rep[il - 1].Rows[ic - 1].ItemArray[60]) && Convert.ToDouble(dt_rep[il - 1].Rows[ic - 1].ItemArray[60]) < 0)
                                {
                                    string lcAddrCell = "BI" + ic3.ToString();
                                    oExlRange = oExcelSheet.get_Range(lcAddrCell, Type.Missing);
                                    oExlRange.Font.Color = ColorTranslator.ToOle(Color.Red);
                                }
                                if (!Convert.IsDBNull(dt_rep[il - 1].Rows[ic - 1].ItemArray[64]) && Convert.ToDouble(dt_rep[il - 1].Rows[ic - 1].ItemArray[64]) < 0)
                                {
                                    string lcAddrCell = "BM" + ic3.ToString();
                                    oExlRange = oExcelSheet.get_Range(lcAddrCell, Type.Missing);
                                    oExlRange.Font.Color = ColorTranslator.ToOle(Color.Red);
                                }
                            }
                            // закрашиваем черезстрочно серым и желтым
                            double lnic2 = ic/2.0;
                            if (ifi == 1 && Convert.ToInt16(lnic2) == lnic2)
                            {
                                string lcAddrCell = ic3.ToString() + ":" + ic3.ToString();
                                oExlRange = oExcelSheet.get_Range(lcAddrCell, Type.Missing);
                                oExlRange.Interior.Color = ColorTranslator.ToOle(Color.Gainsboro);
                                //желтим AA,AB,AC
                                lcAddrCell = "AA" + ic3.ToString();
                                oExlRange = oExcelSheet.get_Range(lcAddrCell, Type.Missing);
                                oExlRange.Interior.Color = ColorTranslator.ToOle(Color.NavajoWhite);
                                lcAddrCell = "AB" + ic3.ToString();
                                oExlRange = oExcelSheet.get_Range(lcAddrCell, Type.Missing);
                                oExlRange.Interior.Color = ColorTranslator.ToOle(Color.NavajoWhite);
                                lcAddrCell = "AC" + ic3.ToString();
                                oExlRange = oExcelSheet.get_Range(lcAddrCell, Type.Missing);
                                oExlRange.Interior.Color = ColorTranslator.ToOle(Color.NavajoWhite);
                                lcAddrCell = "AD" + ic3.ToString();
                                oExlRange = oExcelSheet.get_Range(lcAddrCell, Type.Missing);
                                oExlRange.Interior.Color = ColorTranslator.ToOle(Color.NavajoWhite);
                                lcAddrCell = "AG" + ic3.ToString();
                                oExlRange = oExcelSheet.get_Range(lcAddrCell, Type.Missing);
                                oExlRange.Interior.Color = ColorTranslator.ToOle(Color.NavajoWhite);
                                lcAddrCell = "AI" + ic3.ToString();
                                oExlRange = oExcelSheet.get_Range(lcAddrCell, Type.Missing);
                                oExlRange.Interior.Color = ColorTranslator.ToOle(Color.NavajoWhite);
                                lcAddrCell = "AJ" + ic3.ToString();
                                oExlRange = oExcelSheet.get_Range(lcAddrCell, Type.Missing);
                                oExlRange.Interior.Color = ColorTranslator.ToOle(Color.NavajoWhite);
                                lcAddrCell = "AM" + ic3.ToString();
                                oExlRange = oExcelSheet.get_Range(lcAddrCell, Type.Missing);
                                oExlRange.Interior.Color = ColorTranslator.ToOle(Color.NavajoWhite);
                                lcAddrCell = "AN" + ic3.ToString();
                                oExlRange = oExcelSheet.get_Range(lcAddrCell, Type.Missing);
                                oExlRange.Interior.Color = ColorTranslator.ToOle(Color.NavajoWhite);
                                lcAddrCell = "AQ" + ic3.ToString();
                                oExlRange = oExcelSheet.get_Range(lcAddrCell, Type.Missing);
                                oExlRange.Interior.Color = ColorTranslator.ToOle(Color.NavajoWhite);
                                lcAddrCell = "AR" + ic3.ToString();
                                oExlRange = oExcelSheet.get_Range(lcAddrCell, Type.Missing);
                                oExlRange.Interior.Color = ColorTranslator.ToOle(Color.NavajoWhite);
                                lcAddrCell = "AT" + ic3.ToString();
                                oExlRange = oExcelSheet.get_Range(lcAddrCell, Type.Missing);
                                oExlRange.Interior.Color = ColorTranslator.ToOle(Color.NavajoWhite);
                                lcAddrCell = "AU" + ic3.ToString();
                                oExlRange = oExcelSheet.get_Range(lcAddrCell, Type.Missing);
                                oExlRange.Interior.Color = ColorTranslator.ToOle(Color.NavajoWhite);
                                lcAddrCell = "AW" + ic3.ToString();
                                oExlRange = oExcelSheet.get_Range(lcAddrCell, Type.Missing);
                                oExlRange.Interior.Color = ColorTranslator.ToOle(Color.NavajoWhite);
                                lcAddrCell = "AX" + ic3.ToString();
                                oExlRange = oExcelSheet.get_Range(lcAddrCell, Type.Missing);
                                oExlRange.Interior.Color = ColorTranslator.ToOle(Color.NavajoWhite);
                                lcAddrCell = "BA" + ic3.ToString();
                                oExlRange = oExcelSheet.get_Range(lcAddrCell, Type.Missing);
                                oExlRange.Interior.Color = ColorTranslator.ToOle(Color.NavajoWhite);
                                lcAddrCell = "BC" + ic3.ToString();
                                oExlRange = oExcelSheet.get_Range(lcAddrCell, Type.Missing);
                                oExlRange.Interior.Color = ColorTranslator.ToOle(Color.NavajoWhite);
                                lcAddrCell = "BF" + ic3.ToString();
                                oExlRange = oExcelSheet.get_Range(lcAddrCell, Type.Missing);
                                oExlRange.Interior.Color = ColorTranslator.ToOle(Color.NavajoWhite);
                                lcAddrCell = "BH" + ic3.ToString();
                                oExlRange = oExcelSheet.get_Range(lcAddrCell, Type.Missing);
                                oExlRange.Interior.Color = ColorTranslator.ToOle(Color.NavajoWhite);
                                lcAddrCell = "BI" + ic3.ToString();
                                oExlRange = oExcelSheet.get_Range(lcAddrCell, Type.Missing);
                                oExlRange.Interior.Color = ColorTranslator.ToOle(Color.NavajoWhite);
                                lcAddrCell = "BM" + ic3.ToString();
                                oExlRange = oExcelSheet.get_Range(lcAddrCell, Type.Missing);
                                oExlRange.Interior.Color = ColorTranslator.ToOle(Color.NavajoWhite);
                            }
                        }
                    }

                    //рисуем рамки
                    string lcStrEnd = "BN" + (lnCountRow + 3);
                    oExlRange = oExcelSheet.get_Range("A3", lcStrEnd);
                    oExlRange.Borders.LineStyle = 1;

                    //замораживаем
                    oExlRange = oExcelSheet.get_Range("C4", "BN400");
                    oExlRange.Select();
                    oExcel.ActiveWindow.FreezePanes = true;
                    oExlRange = oExcelSheet.get_Range("K1", "K1");
                    oExlRange.Select();

                    // формат чисел в колонках
                    //oExlRange = oExcelSheet.get_Range("A4", "J400");
                    //oExlRange.NumberFormat = "@";
                    oExlRange = oExcelSheet.get_Range("K4", "Z400");
                    oExlRange.NumberFormat = "# ##0";
                    oExlRange = oExcelSheet.get_Range("AA4", "BN400");
                    oExlRange.NumberFormat = "# ##0,00";
                    oExlRange = oExcelSheet.get_Range("AH4", "AH400");
                    oExlRange.NumberFormat = "# ##0";
                    oExlRange = oExcelSheet.get_Range("AY4", "AY400");
                    oExlRange.NumberFormat = "# ##0,000";
                    oExlRange = oExcelSheet.get_Range("BB4", "BB400");
                    oExlRange.NumberFormat = "# ##0,000";
                    oExlRange = oExcelSheet.get_Range("BK4", "BM400");
                    oExlRange.NumberFormat = "# ##0";
                }

            }
            finally
            {
                oExcel.ScreenUpdating = true;   // включаем обновляемость
                oExcel.Visible = true;    // включаем видимость объекта
            }
        }

        private void CallBackFuncExc(IAsyncResult aRes)
        {
            del_exc.EndInvoke(aRes);
        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
           
        }

        private void panel1_Paint(object sender, PaintEventArgs e)
        {

        }



    }
}